package com.xyf.sys.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URL;
import java.net.URLConnection;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.opencsv.CSVWriter;
import com.xyf.sys.mapper.SysDownloadTaskMapper;
import com.xyf.sys.service.IOrderService;
import static com.xyf.sys.service.impl.DownloadTaskServiceImpl.culActualCost;
import static com.xyf.sys.service.impl.DownloadTaskServiceImpl.getDueToLeader;
import static com.xyf.sys.service.impl.DownloadTaskServiceImpl.getProfitFormula;
import com.xyf.sys.vo.OrderBill;

import lombok.extern.slf4j.Slf4j;

@Component
@Slf4j
public class ExcelToCsv {

    @Autowired
    private SysDownloadTaskMapper taskMapper;

    @Autowired
    private IOrderService orderService;
//    public static void main(String[] args) {
//        String excelFilePath = "F:\\WeChat Files\\wxid_7801268009612\\FileStorage\\File\\2025-02\\快递.xlsx"; // Excel 文件路径
//        String csvFilePath = "F:\\WeChat Files\\wxid_7801268009612\\FileStorage\\File\\2025-02\\output.csv";   // CSV 文件路径
//
//        // 读取 Excel 并写入 CSV（包含表头）
//        convertExcelToCsv(excelFilePath, csvFilePath);
//    }


    public void convertExcelToCsv(String fileUrl, String csvFilePath) throws Exception {
        // 用于存储 Excel 数据的监听器
        ExcelDataListener listener = new ExcelDataListener();

        URL url = new URL(fileUrl);
        URLConnection connection = url.openConnection();
        // 读取 Excel 文件
        EasyExcel.read(connection.getInputStream(), listener).sheet().doRead();
        checkBill(listener.getHeader(), listener.getData());
        // 将读取到的数据（包括表头）写入 CSV 文件
        writeToCsv(csvFilePath, listener.getHeader(), listener.getData());
    }

    private void checkBill(List<String> header, List<Map<Integer, String>> data) {
        DecimalFormat format = new DecimalFormat("#.00");
        format.setMaximumFractionDigits(2);
        // 设置进位方式
        format.setRoundingMode(RoundingMode.HALF_UP);
        header.add(0, "团长");
        header.add(4, "打单重量");
        header.add(5, "重量差");
        header.add(6, "零售价");
        header.add(7, "应付团长");
        header.add(8, "实际成本");
        header.add(9, "利润");
        header.add(10, "团长利润公式");
        header.add(11, "价格方案");
        for (Map<Integer, String> row : data) {
            String orderCode = row.get(0);
            Double billWeight = StringUtils.isNotBlank(row.get(1)) ? Double.parseDouble(row.get(1)) : 0.0;
            Double overWeight = StringUtils.isNotBlank(row.get(2)) ? Double.parseDouble(row.get(2)) : 0.0;
            if (StringUtils.isBlank(orderCode) || billWeight == null) {
                addAndShift(row, 0, "《运单不存在》");
                addAndShift(row, 4, "");
                addAndShift(row, 5, "");
                addAndShift(row, 6, "");
                addAndShift(row, 7, "");
                addAndShift(row, 8, "");
                addAndShift(row, 9, "");
                addAndShift(row, 10, "");
                addAndShift(row, 11, "");
                continue;
            }
            OrderBill orderBill = orderService.findByWaybillId(orderCode.trim());
            if (orderBill != null) {
                BigDecimal weight = orderBill.getWeight() == null ? BigDecimal.ZERO : orderBill.getWeight();
                BigDecimal price = orderBill.getPrice() == null ? BigDecimal.ZERO : orderBill.getPrice().multiply(new BigDecimal("0.01"));
                Double dueToLeader = getDueToLeader(orderBill.getReceiverProvince(), price.doubleValue(), weight.setScale(0, RoundingMode.CEILING).doubleValue(), orderBill.getDeliveryId(), orderBill.getLogisticPriceSchemeId(), orderBill.getCreateTime());
                String profitFormula = getProfitFormula(orderBill.getReceiverProvince(), price.doubleValue(), dueToLeader, orderBill.getLogisticPriceSchemeId(), weight.setScale(0, RoundingMode.CEILING).intValue(), orderBill.getDeliveryId(), orderBill.getCreateTime());
                BigDecimal actualCost = culActualCost(orderBill.getDeliveryId(), overWeight);
                BigDecimal profit = price.subtract(new BigDecimal(dueToLeader.toString())).subtract(actualCost);
                addAndShift(row, 0, orderBill.getName());
                addAndShift(row, 4, orderBill.getWeight() == null ? "" :orderBill.getWeight().toPlainString());
                addAndShift(row, 5, Double.toString(Double.valueOf(weight.toString()) - billWeight));
                addAndShift(row, 6, Double.toString(price.doubleValue()));
                addAndShift(row, 7, Double.toString(Double.parseDouble(format.format(dueToLeader))));
                addAndShift(row, 8, Double.toString(Double.parseDouble(format.format(actualCost))));
                addAndShift(row, 9, profitFormula);
                addAndShift(row, 10, Double.toString(Double.parseDouble(format.format(profit))));
                addAndShift(row, 11, orderBill.getLogisticPriceSchemeId() == null ? "" : orderBill.getLogisticPriceSchemeId().toString());
            } else {
                addAndShift(row, 0, "《运单不存在》");
                addAndShift(row, 4, "");
                addAndShift(row, 5, "");
                addAndShift(row, 6, "");
                addAndShift(row, 7, "");
                addAndShift(row, 8, "");
                addAndShift(row, 9, "");
                addAndShift(row, 10, "");
                addAndShift(row, 11, "");
            }
        }
    }

    public void writeToCsv(String csvFilePath, List<String> header, List<Map<Integer, String>> data) {
        try (FileOutputStream fos = new FileOutputStream(csvFilePath);
             OutputStreamWriter osw = new OutputStreamWriter(fos, StandardCharsets.UTF_8);
             CSVWriter writer = new CSVWriter(osw)) {

            // 写入 BOM（字节顺序标记），确保 Excel 正确识别 UTF-8 编码
            fos.write(0xEF);
            fos.write(0xBB);
            fos.write(0xBF);

            // 写入表头
            writer.writeNext(header.toArray(new String[0]));

            // 写入数据行
            for (Map<Integer, String> row : data) {
                String[] rowData = row.values().toArray(new String[0]);
                String[] subarray = ArrayUtils.subarray(rowData, 0, header.size());
                writer.writeNext(subarray);
            }
            System.out.println("CSV 文件已生成: " + csvFilePath);
        } catch (IOException e) {
            System.err.println("写入 CSV 文件时出错: " + e.getMessage());
        }
    }

    // Excel 数据监听器
    public class ExcelDataListener extends AnalysisEventListener<Map<Integer, String>> {
        private List<Map<Integer, String>> data = new ArrayList<>();
        private List<String> header = new ArrayList<>();

        @Override
        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
            // 读取表头
            header.addAll(headMap.values());
        }

        @Override
        public void invoke(Map<Integer, String> row, AnalysisContext context) {
            // 读取数据行
            data.add(row);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            System.out.println("Excel 文件读取完成");
        }

        public List<String> getHeader() {
            return header;
        }

        public List<Map<Integer, String>> getData() {
            return data;
        }
    }

    /**
     * 向 Map 中添加一个元素，并将指定 key 及其后续 key 的值向后移动
     *
     * @param map   目标 Map
     * @param key   要插入的 key
     * @param value 要插入的值
     */
    public static void addAndShift(Map<Integer, String> map, int key, String value) {
        // 找到 Map 中的最大 key
        int maxKey = Collections.max(map.keySet());

        // 从最大 key 开始，向后移动键值对
        for (int i = maxKey; i >= key; i--) {
            map.put(i + 1, map.get(i)); // 将 key = i 的值移动到 key = i + 1
        }

        // 插入新的键值对
        map.put(key, value);
    }
}